Using Transaction in SSIS
Categories: Database; Tagged with: SSIS • Transaction; @ October 30th, 2012 18:07Transaction is a build-in feature in SSIS, all containers(i.e, package, sequence container), and components support transaction. Support Level: NotSupported, Supported, and Required.
-
Required indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction. For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.
-
Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.
-
NotSupported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.
— http://msdn.microsoft.com/en-us/library/ms137690.aspx
For example:
Here is a sequence container, 3 steps: ‘Excute SQL Task’ to clear destination data, ‘Data Flow Task’ load data from source and insert data into destination, ‘Script Task’ just a script task which will fail always.
Without transaction:
config the sequence container property: TransactionOption = NotSupported, all components TransactionOption keep default(Supported), then run the package:
the first two component run successfully, the destination table has been cleared and the new data inserted, even through the last task failed.
Using transaction
Change ‘Sequence Container’ TransactionOption=Required, then run the package.
The last one failed, because they are in one transaction, so the destination will keep no change.
SSIS 使用EventHandler处理错误 Error Handling using Event Handler <->